﻿using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;

namespace DataCoreTechnologies.SqlServer
{
   /// <summary>
   /// Contains information about a database belonging to a server.
   /// </summary>
   public sealed partial class Database
   {
      #region Public
      //Properties
      /// <summary>
      /// The time this database's information was loaded into memory.
      /// </summary>
      public DateTime? LoadedOn { get; private set; }
      /// <summary>
      /// A number that uniquely identifies thist class instance.
      /// </summary>
      public Guid InstanceId { get; internal set; }
      /// <summary>
      /// The server that this database belongs to.
      /// </summary>
      public Server Server { get; internal set; }
      /// <summary>
      /// Indicator stating whether this database's objects have been loaded into memory.
      /// </summary>
      public Boolean IsInformationLoaded { get; private set; }
      /// <summary>
      /// A title used to uniquely identify this database within the server.
      /// </summary>
      public string Name { get; internal set; }
      /// <summary>
      /// A number that identifies the database that this database was derived from.
      /// </summary>
      public Nullable<int> SourceDatabaseId { get; internal set; }
      /// <summary>
      /// A string indicating who the owner of this database is.
      /// </summary>
      public string Owner { get; internal set; }
      /// <summary>
      /// The date this database was created.
      /// </summary>
      public DateTime CreateDate { get; internal set; }
      /// <summary>
      /// The compatibility level of this database.
      /// </summary>
      public int CompatibilityLevel { get; internal set; }
      /// <summary>
      /// The default collation name for objects contained in this database.
      /// </summary>
      public string CollationName { get; internal set; }
      /// <summary>
      /// The user access security levels used by this database.
      /// </summary>
      public int UserAccess { get; internal set; }
      /// <summary>
      /// A short description of the user access security levels used by this database.
      /// </summary>
      public string UserAccessDescription { get; internal set; }
      /// <summary>
      /// Indicator stating whether this database is read only.
      /// </summary>
      public Boolean IsReadOnly { get; internal set; }
      /// <summary>
      /// Indicator stating whether this database's auto close option is on.
      /// </summary>
      public Boolean IsAutoCloseOn { get; internal set; }
      /// <summary>
      /// Indicator stating whether this database's auto shrink option is on.
      /// </summary>
      public Boolean IsAutoShrinkOn { get; internal set; }
      /// <summary>
      /// The current state of this database.
      /// </summary>
      public int State { get; internal set; }
      /// <summary>
      /// A short description of the current state of this database.
      /// </summary>
      public string StateDescription { get; internal set; }
      /// <summary>
      /// Indicator stating whether this database is in standby mode.
      /// </summary>
      public Boolean IsInStandby { get; internal set; }
      /// <summary>
      /// Indicator stating whether this database has been cleanly shutdown.
      /// </summary>
      public Boolean IsCleanlyShutdown { get; internal set; }
      /// <summary>
      /// Indicator stating whether this database has supplemental logging enabled.
      /// </summary>
      public Boolean IsSupplementalLoggingEnabled { get; internal set; }
      /// <summary>
      /// The snapshot isolation state for this database.
      /// </summary>
      public int SnapshotIsolationState { get; internal set; }
      /// <summary>
      /// A description of the snapshop isolation state for this database.
      /// </summary>
      public string SnapshotIsolationStateDescription { get; internal set; }
      /// <summary>
      /// Indicator stating whether the read committed snapshop is on.
      /// </summary>
      public Boolean IsReadCommittedSnapshotOn { get; internal set; }
      /// <summary>
      /// The recovery model this database is set to.
      /// </summary>
      public int RecoveryModel { get; internal set; }
      /// <summary>
      /// A description of the recovery model this database is set to.
      /// </summary>
      public string RecoveryModelDescription { get; internal set; }
      /// <summary>
      /// The option used by verify pages.
      /// </summary>
      public int PageVerifyOption { get; internal set; }
      /// <summary>
      /// A description of the option used to verify pages.
      /// </summary>
      public string PageVerifyOptionDescription { get; internal set; }
      /// <summary>
      /// Indicator stating if statistics are updated on create.
      /// </summary>
      public Boolean IsAutoCreateStatsOn { get; internal set; }
      /// <summary>
      /// Indicator stating if statistics are updated on update.
      /// </summary>
      public Boolean IsAutoUpdateStatsOn { get; internal set; }
      /// <summary>
      /// Indicator stating if statistics are updated asynchronously on create.
      /// </summary>
      public Boolean IsAutoUpdateStatsAsyncOn { get; internal set; }
      /// <summary>
      /// Indicator stating if ansi null default is on.
      /// </summary>
      public Boolean IsAnsiNullDefaultOn { get; internal set; }
      /// <summary>
      /// Indicator stating if ansi nulls are on.
      /// </summary>
      public Boolean IsAnsiNullsOn { get; internal set; }
      /// <summary>
      /// Indicator stating if ansi padding is on.
      /// </summary>
      public Boolean IsAnsiPaddingOn { get; internal set; }
      /// <summary>
      /// Indicator stating if ansi warnings are on.
      /// </summary>
      public Boolean IsAnsiWarningsOn { get; internal set; }
      /// <summary>
      /// Indicator stating if arithmatic abort is on.
      /// </summary>
      public Boolean IsArithAbortOn { get; internal set; }
      /// <summary>
      /// Indicator stating if concat null yields null is on.
      /// </summary>
      public Boolean IsConcatNullYieldsNullOn { get; internal set; }
      /// <summary>
      /// Indicator stating if numeric round abort is on.
      /// </summary>
      public Boolean IsNumericRoundAbortOn { get; internal set; }
      /// <summary>
      /// Indicator stating if quoted identifies are on.
      /// </summary>
      public Boolean IsQuotedIdentifierOn { get; internal set; }
      /// <summary>
      /// Indicator stating if recursive triggers are on.
      /// </summary>
      public Boolean IsRecursiveTriggersOn { get; internal set; }
      /// <summary>
      /// Indicator stating if cursor close on commit is on.
      /// </summary>
      public Boolean IsCursorCloseOnCommitOn { get; internal set; }
      /// <summary>
      /// Indicator stating if cursors are local by default
      /// </summary>
      public Boolean IsLocalCursorDefault { get; internal set; }
      /// <summary>
      /// Indicator stating whether full text has been enabled.
      /// </summary>
      public Boolean IsFullTextEnabled { get; internal set; }
      /// <summary>
      /// Indicator stating whether is trust worthy is on.
      /// </summary>
      public Boolean IsTrustWorthyOn { get; internal set; }
      /// <summary>
      /// Indicator stating whether database chaining has been enabled.
      /// </summary>
      public Boolean IsDBChainingOn { get; internal set; }
      /// <summary>
      /// Indicator stating if parameterization is being enforced.
      /// </summary>
      public Boolean IsParameterizationForced { get; internal set; }
      /// <summary>
      /// Indicator stating if the master key is being encrypted by the server.
      /// </summary>
      public Boolean IsMasterKeyEncryptedByServer { get; internal set; }
      /// <summary>
      /// Indicator stating whether this database is published for replication.
      /// </summary>
      public Boolean IsPublished { get; internal set; }
      /// <summary>
      /// Indicator stating whether this database is subscribed for replication.
      /// </summary>
      public Boolean IsSubscribed { get; internal set; }
      /// <summary>
      /// Indicator stating whether this database is merge published for replication.
      /// </summary>
      public Boolean IsMergePublished { get; internal set; }
      /// <summary>
      /// Indicator stating whether this database is a distributor for replication.
      /// </summary>
      public Boolean IsDistributor { get; internal set; }
      /// <summary>
      /// Indicator stating whether syncronization with backup is enabled.
      /// </summary>
      public Boolean IsSyncWithBackup { get; internal set; }
      /// <summary>
      /// The service broker guid for this database.
      /// </summary>
      public Guid ServiceBrokerGuid { get; internal set; }
      /// <summary>
      /// Indicator stating whether broker is enabled for this database.
      /// </summary>
      public Boolean IsBrokerEnabled { get; internal set; }
      /// <summary>
      /// The log reuse wait time.
      /// </summary>
      public int LogReuseWait { get; internal set; }
      /// <summary>
      /// A description of the log reuse wait time.
      /// </summary>
      public string LogReuseWaitDescription { get; internal set; }
      /// <summary>
      /// Indicator stating whether date correlation is on.
      /// </summary>
      public Boolean IsDateCorrelationOn { get; internal set; }
      /// <summary>
      /// The approximate amount of disk space this database uses.
      /// </summary>
      public Int64 DatabaseSize
      {
         get { return DataSpaceAvailable + DataSpaceUsed + IndexSpaceUsed; }
      }
      /// <summary>
      /// The approximate amount of disk space the data on this database uses.
      /// </summary>
      public Int64 DataSpaceUsed { get; internal set; }
      /// <summary>
      /// The approximate amount of disk space the indexes on this database use.
      /// </summary>
      public Int64 IndexSpaceUsed { get; internal set; }
      /// <summary>
      /// The approximate amount of disk space available for this database.
      /// </summary>
      public Int64 DataSpaceAvailable { get; internal set; }
      /// <summary>
      /// A short description explaining the purpose of this database.
      /// </summary>
      public string Description { get; internal set; }
      /// <summary>
      /// A collection of schemas belonging to this database.
      /// </summary>
      public IEnumerable<Schema> Schemas
      {
         get
         {
             for (int index = 0; index < _schemas.Count; index++)
             {
                 yield return _schemas[index];
             }
         }
      }
      /// <summary>
      /// A collection of system types belonging to this database.
      /// </summary>
      public IEnumerable<SystemType> SystemTypes
      {
         get
         {
             for (int index = 0; index < _systemTypes.Count; index++)
             {
                 yield return _systemTypes[index];
             }
         }
      }
      /// <summary>
      /// A collection of triggers belonging to this database.
      /// </summary>
      public IEnumerable<Trigger> Triggers
      {
         get
         {
             for (int index = 0; index < _triggers.Count; index++)
             {
                 yield return _triggers[index];
             }
         }
      }
      /// <summary>
      /// A collection of extended properties belonging to this database.
      /// </summary>
      public IEnumerable<ExtendedProperty> ExtendedProperties
      {
         get
         {
             for (int index = 0; index < _extendedProperties.Count; index++)
             {
                 yield return _extendedProperties[index];
             }
         }
      }
      //Methods
      /// <summary>
      /// Loads all of meta data for all of the objects contained within this database.
      /// </summary>
      public void LoadDatabaseInformation()
      {
         LoadedOn = DateTime.Now;

         Server.DefaultDatabase = Name;
         Server.ResetConnection();

         ClearDatabase();
         #region 2000
         if (Server.Version.Substring(0, 1) == "8")
         {
            Diagnostic.WriteLine("Started loading 2000 Schemas");
            Load2000_Schemas();
            Diagnostic.WriteLine("Finished loading 2000 Schemas");
            Diagnostic.WriteLine("Started loading 2000 System Types");
            Load2000_SystemTypes();
            Diagnostic.WriteLine("Finished loading 2000 System Types");
            Diagnostic.WriteLine("Started loading 2000 User Types");
            Load2000_UserTypes();
            Diagnostic.WriteLine("Finished loading 2000 User Types");
            Diagnostic.WriteLine("Started loading 2000 Database Objects");
            Load2000_DatabaseObjects();
            Diagnostic.WriteLine("Finished loading 2000 Database Objects");
            Diagnostic.WriteLine("Started loading 2000 Columns and Parameters");
            Load2000_ColumnsAndParameters();
            Diagnostic.WriteLine("Finished loading 2000 Columns and Parameters");
            Diagnostic.WriteLine("Started loading 2000 Check Constraints and Default Values");
            Load2000_CheckConstraintsAndDefaultValues();
            Diagnostic.WriteLine("Finished loading 2000 Check Constraints and Default Values");
            Diagnostic.WriteLine("Started loading 2000 Triggers");
            Load2000_Triggers();
            Diagnostic.WriteLine("Finished loading 2000 Triggers");
            Diagnostic.WriteLine("Started loading 2000 Indexes");
            Load2000_Indexes();
            Diagnostic.WriteLine("Finished loading 2000 Indexes");
            Diagnostic.WriteLine("Started loading 2000 Primary Keys");
            Load2000_PrimaryKeys();
            Diagnostic.WriteLine("Finished loading 2000 Primary Keys");
            Diagnostic.WriteLine("Started loading 2000 Foreign Keys");
            Load2000_ForeignKeys();
            Diagnostic.WriteLine("Finished loading 2000 Foreign Keys");
            Diagnostic.WriteLine("Started loading 2000 Extended Properties");
            Load2000_ExtendedProperties();
            Diagnostic.WriteLine("Finished loading 2000 Extended Properties");
            Diagnostic.WriteLine("Started loading 2000 Object Sizes");
            Load2000_ObjectSizes();
            Diagnostic.WriteLine("Finished loading 2000 Object Sizes");
         }
         #endregion
         #region 2005
         else if (Server.Version.Substring(0, 1) == "9")
         {
            Diagnostic.WriteLine("Started loading 2005 Schemas");
            Load2005_Schemas();
            Diagnostic.WriteLine("Finished loading 2005 Schemas");
            Diagnostic.WriteLine("Started loading 2005 System Types");
            Load2005_SystemTypes();
            Diagnostic.WriteLine("Finished loading 2005 System Types");
            Diagnostic.WriteLine("Started loading 2005 User Types");
            Load2005_UserTypes();
            Diagnostic.WriteLine("Finished loading 2005 User Types");
            Diagnostic.WriteLine("Started loading 2005 Database Objects");
            Load2005_DatabaseObjects();
            Diagnostic.WriteLine("Finished loading 2005 Database Objects");
            Diagnostic.WriteLine("Started loading 2005 Columns");
            Load2005_Columns();
            Diagnostic.WriteLine("Finished loading 2005 Columns");
            Diagnostic.WriteLine("Started loading 2005 Default Values");
            Load2005_DefaultValues();
            Diagnostic.WriteLine("Finished loading 2005 Default Values");
            Diagnostic.WriteLine("Started loading 2005 Indexes");
            Load2005_Indexes();
            Diagnostic.WriteLine("Finished loading 2005 Indexes");
            Diagnostic.WriteLine("Started loading 2005 Primary Keys");
            Load2005_PrimaryKeys();
            Diagnostic.WriteLine("Finished loading 2005 Primary Keys");
            Diagnostic.WriteLine("Started loading 2005 Foreign Keys");
            Load2005_ForeignKeys();
            Diagnostic.WriteLine("Finished loading 2005 Foreign Keys");
            Diagnostic.WriteLine("Started loading 2005 Check Constraints");
            Load2005_CheckConstraints();
            Diagnostic.WriteLine("Finished loading 2005 Check Constraints");
            Diagnostic.WriteLine("Started loading 2005 Parameters");
            Load2005_Parameters();
            Diagnostic.WriteLine("Finished loading 2005 Parameters");
            Diagnostic.WriteLine("Started loading 2005 Triggers");
            Load2005_Triggers();
            Diagnostic.WriteLine("Finished loading 2005 Triggers");
            Diagnostic.WriteLine("Started loading 2005 Database Sizes");
            Load2005_DatabaseSize();
            Diagnostic.WriteLine("Finished loading 2005 Database Sizes");
            Diagnostic.WriteLine("Started loading 2005 Table Sizes");
            Load2005_TableSizes();
            Diagnostic.WriteLine("Finished loading 2005 Table Sizes");
            Diagnostic.WriteLine("Started loading 2005 Stored Procedures DDL");
            Load2005_StoredProceduresCode();
            Diagnostic.WriteLine("Finished loading 2005 Stored Procedures DDL");
            Diagnostic.WriteLine("Started loading 2005 Functions DDL");
            Load2005_FunctionsCode();
            Diagnostic.WriteLine("Finished loading 2005 Functions DDL");
            Diagnostic.WriteLine("Started loading 2005 Views DDL");
            Load2005_ViewsCode();
            Diagnostic.WriteLine("Finished loading 2005 Views DDL");
            Diagnostic.WriteLine("Started loading 2005 Extended Properties");
            Load2005_ExtendedProperties();
            Diagnostic.WriteLine("Finished loading 2005 Extended Properties");
            Diagnostic.WriteLine("");
         }
         #endregion
         #region 2008
         else if (Server.Version.Substring(0, 2) == "10")
         {
            Diagnostic.WriteLine("Started loading 2008 Schemas");
            Load2005_Schemas();
            Diagnostic.WriteLine("Finished loading 2008 Schemas");
            Diagnostic.WriteLine("Started loading 2008 System Types");
            Load2005_SystemTypes();
            Diagnostic.WriteLine("Finished loading 2008 System Types");
            Diagnostic.WriteLine("Started loading 2008 User Types");
            Load2005_UserTypes();
            Diagnostic.WriteLine("Finished loading 2008 User Types");
            Diagnostic.WriteLine("Started loading 2008 Database Objects");
            Load2005_DatabaseObjects();
            Diagnostic.WriteLine("Finished loading 2008 Database Objects");
            Diagnostic.WriteLine("Started loading 2008 Columns");
            Load2005_Columns();
            Diagnostic.WriteLine("Finished loading 2008 Columns");
            Diagnostic.WriteLine("Started loading 2008 Default Values");
            Load2005_DefaultValues();
            Diagnostic.WriteLine("Finished loading 2008 Default Values");
            Diagnostic.WriteLine("Started loading 2008 Indexes");
            Load2005_Indexes();
            Diagnostic.WriteLine("Finished loading 2008 Indexes");
            Diagnostic.WriteLine("Started loading 2008 Primary Keys");
            Load2005_PrimaryKeys();
            Diagnostic.WriteLine("Finished loading 2008 Primary Keys");
            Diagnostic.WriteLine("Started loading 2008 Foreign Keys");
            Load2005_ForeignKeys();
            Diagnostic.WriteLine("Finished loading 2008 Foreign Keys");
            Diagnostic.WriteLine("Started loading 2008 Check Constraints");
            Load2005_CheckConstraints();
            Diagnostic.WriteLine("Finished loading 2008 Check Constraints");
            Diagnostic.WriteLine("Started loading 2008 Parameters");
            Load2005_Parameters();
            Diagnostic.WriteLine("Finished loading 2008 Parameters");
            Diagnostic.WriteLine("Started loading 2008 Triggers");
            Load2005_Triggers();
            Diagnostic.WriteLine("Finished loading 2008 Triggers");
            Diagnostic.WriteLine("Started loading 2008 Database Sizes");
            Load2005_DatabaseSize();
            Diagnostic.WriteLine("Finished loading 2008 Database Sizes");
            Diagnostic.WriteLine("Started loading 2008 Table Sizes");
            Load2005_TableSizes();
            Diagnostic.WriteLine("Finished loading 2008 Table Sizes");
            Diagnostic.WriteLine("Started loading 2008 Stored Procedures DDL");
            Load2005_StoredProceduresCode();
            Diagnostic.WriteLine("Finished loading 2008 Stored Procedures DDL");
            Diagnostic.WriteLine("Started loading 2008 Functions DDL");
            Load2005_FunctionsCode();
            Diagnostic.WriteLine("Finished loading 2008 Functions DDL");
            Diagnostic.WriteLine("Started loading 2008 Views DDL");
            Load2005_ViewsCode();
            Diagnostic.WriteLine("Finished loading 2008 Views DDL");
            Diagnostic.WriteLine("Started loading 2008 Extended Properties");
            Load2005_ExtendedProperties();
            Diagnostic.WriteLine("Finished loading 2008 Extended Properties");
            Diagnostic.WriteLine("");
         }
         #endregion

         IsInformationLoaded = true;
      }
      #endregion Public
      #region Internal
      //Constructor
      /// <summary>
      /// Creates a database and associates it with the server specified by the parameter.
      /// </summary>
      /// <param name="container">The server containing this database.</param>
      internal Database(Server container)
      {
         InstanceId = System.Guid.NewGuid();
         IsInformationLoaded = false;
         Server = container;
         LoadedOn = null;
      }
      //Properties
      /// <summary>
      /// A number used to uniquely identify this database on the server.
      /// </summary>
      internal int Id { get; set; }
      //Methods
      /// <summary>
      /// Associates a schema with this database.
      /// </summary>
      /// <param name="schema">The schema that belongs to this database.</param>
      internal void AddSchema(Schema schema) { _schemas.Add(schema); }
      /// <summary>
      /// Associates a system type with this database.
      /// </summary>
      /// <param name="systemType">The system type that belongs to this database.</param>
      internal void AddSystemType(SystemType systemType) { _systemTypes.Add(systemType); }
      /// <summary>
      /// Associates a trigger with this database.
      /// </summary>
      /// <param name="trigger">The trigger that belongs to this database.</param>
      internal void AddTrigger(Trigger trigger) { _triggers.Add(trigger); }
      #endregion Internal
      #region Private
      //Properties
      private List<Schema> _schemas = new List<Schema>();
      private List<SystemType> _systemTypes = new List<SystemType>();
      private List<Trigger> _triggers = new List<Trigger>();
      private List<ExtendedProperty> _extendedProperties = new List<ExtendedProperty>();
      //Methods
      private void ClearDatabase()
      {
        _schemas.Clear();
        _systemTypes.Clear();
        _triggers.Clear();
        _extendedProperties.Clear();
      }
      #endregion Private
   }
}
